/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package lcfgly.database.utils;

import java.util.regex.Matcher;
import java.util.regex.Pattern;
import lcfgly.utils.LTypeUtils;
import static lcfgly.utils.LTypeUtils.stringIsEmpty;

/**
 * LSelectDecode 简单解析Sql语句
 * 此类并不确保能完全正确解析使用时请注意
 * @author lcfgly
 * @Date 2015-1-28 9:29:46
 * @version 1.0
 */
public class LSelectDecode {

    private String prefix = "";
    private boolean isFind = false;
    private StringBuilder _select = null;
    private StringBuilder _from = null;
    private StringBuilder _where = null;
    private StringBuilder _groupby = null;
    private StringBuilder _orderby = null;
    private StringBuilder _limit = null;
    private String oldStr;

    /**
     * 获取sql中的select
     * @return 
     */
    public StringBuilder getSelect() {
        return _select;
    }

    /**
     * 获取sql中的From
     * @return 
     */
    public StringBuilder getFrom() {
        return _from;
    }

    /**
     * 获取sql中的Where
     * @return 
     */
    public StringBuilder getWhere() {
        if(_where == null){
            _where=new StringBuilder();
        }
        return _where;
    }

    /**
     * 获取sql中的OrderBy
     * @return 
     */
    public StringBuilder getOrder() {
        if(_orderby==null){
            _orderby=new StringBuilder();
        }
        return _orderby;
    }

    /**
     * 获取sql中的GroupBy
     * @return 
     */
    public StringBuilder getGroup() {
        if(_groupby==null){
            _groupby=new StringBuilder();
        }
        return _groupby;
    }

    /**
     * 获取sql中limit
     * @return 
     */
    public StringBuilder getLimit() {
        if(_limit == null){
            _limit=new StringBuilder();
        }
        return _limit;
    }
    
    /**
     * 解析sql是否正常
     * @return 
     */
    public boolean find(){
        return isFind;
    }
    
    public LSelectDecode(String sql, String prefix) {
        oldStr = sql;
        if (prefix != null) {
            this.prefix = prefix;
        }
        DSelect(sql);
    }

    /**
     * 重置sql
     * @param sql 
     */
    public void setSQL(String sql){
        oldStr=sql;
        isFind=false;
        DSelect(sql);
    }
    
    /**
     * 设置数据库前缀
     * @param prefix 
     */
    public void setPrefix(String prefix){
        if(prefix != null)
            this.prefix=prefix;
    }
    
    private void DSelect(String sql) {
        Pattern p=Pattern.compile("(?i)select (.*?) from (.*)");
        Matcher m=p.matcher(sql);
        isFind=m.find();
        if(isFind){
            HSelect(m.group(1));
            DFrom(m.group(2));
        }
    }

    /**
     * 给数据表添加前缀
     * @param tablename
     * @return 
     */
    private String addPrefix(String tablename){
        if(LTypeUtils.stringIsEmpty(prefix))
            return tablename;
        else if(LTypeUtils.stringIsEmpty(tablename))
            return "";
        tablename=tablename.trim();
        if(tablename.startsWith(prefix))
            return tablename;
        else
            return prefix+tablename;
    }
    
    /**
     * 获取stringbuilder
     * @param sql 
     */
    private StringBuilder getStringBuilder(StringBuilder sql){
        if(sql == null)
            sql=new StringBuilder();
        else
            sql.setLength(0);
        return sql;
    }
    
    /**
     * 解析select中是否含有表名，如果有加上数据库前缀
     * @param sql 
     */
    private void HSelect(String sql) {
        _select=getStringBuilder(_select);
        if (!LTypeUtils.stringIsEmpty(sql)) {
            Hp(sql, _select);
        } else {
            _select.append(sql);
        }
    }

    /**
     * 按逗号分割的字符串如果含有数据表，则数据表加前缀
     * @param sql 字符串
     * @param sdb 解析后数据存放点
     */
    private void Hp(String sql, StringBuilder sdb) {
        sql = sql.trim();
        String[] sqle = sql.split(",");
        int num = sqle.length;
        for (int i = 0; i < num; i++) {
            String[] sd = sqle[i].split("\\.");
            if (sd.length > 1) {
                sqle[i]=addPrefix(sqle[i]);
            }
            sdb.append(sqle[i]);
            if (i != num - 1) {
                sdb.append(",");
            }
        }
    }
    
    private void HpFrom(String sql, StringBuilder sdb) {
        if (sql != null) {
            sql = sql.trim();
            String[] sqle = sql.split(",");
            int num = sqle.length;
            for (int i = 0; i < num; i++) {
                sdb.append(addPrefix(sqle[i]));
                if (i != num - 1) {
                    sdb.append(",");
                }
            }
        }
    }

    private void DFrom(String sql) {
        Pattern p=Pattern.compile("(?i)(.*?)(group by|order by|limit|where)(.*)");
        Matcher m=p.matcher(sql);
        if(m.find()){
            HFrom(m.group(1));
            DSwitch(m.group(2).toLowerCase(), m.group(3));
        }else{
            HFrom(sql);
        }
    }

    /**
     * 解析from语句是否含有表明，如果有加上数据库前缀
     * @param sql 
     */
    private void HFrom(String sql) {
        _from=getStringBuilder(_from);
        if (!LTypeUtils.stringIsEmpty(prefix)) {
            Pattern p=Pattern.compile("(?i)(.*?) (LEFT\\s|RIGHT\\s|FULL\\s)?JOIN (.*) ON (.*)");
            Matcher m=p.matcher(sql);
            if(m.find()){
                HpFrom(m.group(1), _from);
                _from.append(" ").append(m.group(2).toUpperCase()).append("JOIN ").append(addPrefix(m.group(3).trim())).append(" ON ");
                HWhere(m.group(4), _from);
            } else {
                Hd(sql, _from);
            }
        } else {
            Hd(sql, _from);
        }
    }

    private void Hd(String sql, StringBuilder sdb) {
        String[] s = sql.split(",");
        int num = s.length;
        for (int i = 0; i < num; i++) {
            sdb.append(addPrefix(s[i]));
            if (i != num - 1) {
                sdb.append(",");
            }
        }
    }

     /**
      * 对sql中的条件解析，如果存在表名，则给表名加上前缀
      * @param sql
      * @param sdb 
      */
    private void HWhere(String sql, StringBuilder sdb) {
        sql=sql.trim()+" ";
        Pattern p=Pattern.compile("(?i)(.*) (and|or) (.*)");
        Matcher m=p.matcher(sql);
        String pstr=sql;
        if(m.find()){
            HWhere(m.group(1), sdb);
            sdb.append(" ").append(m.group(2));
            pstr=m.group(3);
        }
        String[] sg=pstr.trim().split("=");
        int num = sg.length;
        for (int i = 0; i < num; i++) {
                String[] sgg = sg[i].split("\\.");
                if (sgg.length > 1) {
                    sdb.append(" ");
                    sg[i]=addPrefix(sg[i]);
                }
                sdb.append(sg[i]);
                if (i != num - 1) {
                    sdb.append(" = ");
                }
        }
    }

    private void DSwitch(String key, String sql) {
        switch (key) {
            case "where":
                DWhere(sql);
                break;
            case "group by":
                DGroupby(sql);
                break;
            case "order by":
                DOrderby(sql);
                break;
            case "limit":
                DLimit(sql);
                break;
        }
    }

    private void DWhere(String sql) {
        Pattern p=Pattern.compile("(?i)(.*?)(group by|order by|limit)(.*)");
        Matcher m=p.matcher(sql);
        _where=getStringBuilder(_where);
        if (m.find()) {
            HWhere(m.group(1), _where);
            DSwitch(m.group(2).toLowerCase(),m.group(3));
        } else {
            HWhere(sql, _where);
        }
    }

    private void DGroupby(String sql) {
        Pattern p=Pattern.compile("(?i)(.*?)(order by|limit)(.*)");
        Matcher m=p.matcher(sql);
        _groupby = getStringBuilder(_groupby);
        if (m.find()) {
            _groupby.append(m.group(1));
            DSwitch(m.group(2).toLowerCase(), m.group(3));
        } else {
            _groupby.append(sql);
        }
    }

    private void DOrderby(String sql) {
        Pattern p=Pattern.compile("(?i)(.*?)(group by|limit)(.*)");
        Matcher m=p.matcher(sql);
        if(m.find()){
            HOrder(m.group(1));
            DSwitch(m.group(2).toLowerCase(), m.group(3));
        } else {
            HOrder(sql);
        }
    }

    private void HOrder(String sql) {
        _orderby = getStringBuilder(_orderby);
        String[] e = sql.split(",");
        int num = e.length;
        for (int i = 0; i < num; i++) {
            String[] s = e[i].split(" ");
            for (String item : s) {
                String[] ss = item.split("\\.");
                if (ss.length > 1) {
                    item=addPrefix(item);
                }
                _orderby.append(item).append(" ");
            }
            if (i != num - 1) {
                _orderby.append(",");
            }
        }
    }

    private void DLimit(String sql) {
        Pattern p=Pattern.compile("(?i)(.*?)(order by|group by)(.*)");
        Matcher m=p.matcher(sql);
        _limit = getStringBuilder(_limit);
        if (m.find()) {
            _limit.append(m.group(1));
            DSwitch(m.group(2).toLowerCase(), m.group(3));
        } else {
            _limit.append(sql);
        }
    }

    public String Sql() {
        if (!isFind) {
            return oldStr;
        }
        StringBuilder result = new StringBuilder("SELECT ");
        result.append(_select).append(" FROM ").append(_from);
        if (_where != null && !stringIsEmpty(_where.toString())) {
            result.append(" WHERE ").append(_where);
        }
        if (_groupby != null && !stringIsEmpty(_groupby.toString())) {
            result.append(" GROUP BY ").append(_groupby);
        }
        if (_orderby != null && !stringIsEmpty(_orderby.toString())) {
            result.append(" ORDER BY ").append(_orderby);
        }
        if (_limit != null && !stringIsEmpty(_limit.toString())) {
            result.append(" LIMIT ").append(_limit);
        }
        return result.toString();
    }

}